
insert into t_dictionay(id,type,name) values('0','4','不显示');
insert into t_dictionay(id,type,name) values('1','4','显示');
insert into t_dictionarytype(type,name) values('4','显示，不显示');

create table t_comment(
pid integer primary key not null,
username varchar(254),
content varchar2(3000),
createtime date,
status varchar(254),
voice varchar(254)
);

create table t_complaint(
id integer primary key not null,
username varchar(254),
complaintcontent varchar2(3000),
complaintdate date,
status varchar(254)
);


INSERT INTO t_services VALUES ('42', '1.5', '1039', '民意警务意见采集', 'insert into t_comment (PID,USERNAME, CONTENT, CREATETIME, STATUS, VOICE) values (PUB_SEQUENCE.NEXTVAL,?, ?, sysdate, ''0'', '''')' , null, 'update', 'local', null, null, '1', null, null);


INSERT INTO t_reqparams VALUES ('42', 'username', '用户名', null, null, null, null, '1');
INSERT INTO t_reqparams VALUES ('42', 'content', '内容', null, null, null, null, '2');

INSERT INTO t_resparams VALUES ('42', 'result', '0:失败1:成功', null, null, null, '1');




INSERT INTO t_services VALUES ('43', '1.5', '1040', '民意警务网上投诉', 
'insert into t_complaint (ID,USERNAME, COMPLAINTCONTENT, COMPLAINTDATE, STATUS) values (PUB_SEQUENCE.NEXTVAL,?, ?, sysdate, ''0'')' 
, null, 'update', 'local', null, null, '1', null, null);

	
INSERT INTO t_reqparams VALUES ('43', 'username', '用户名', null, null, null, null, '1');
INSERT INTO t_reqparams VALUES ('43', 'complaintcontent', '投诉内容', null, null, null, null, '2');

INSERT INTO t_resparams VALUES ('43', 'result', '0:失败1:成功', null, null, null, '1');
-------------------------------------------------- 2014-03-31 -----------------------------------------------------------------------------

alter table t_membernews add (tp blob);

update t_services set execsql='insert into t_membernews(NEWSID,username, content, type, author, phoneNo, 
level1,tp,CREATETIME) values(PUB_SEQUENCE.NEXTVAL,?,?,?,?,?,?,?,sysdate)' where sid='24';

INSERT INTO t_reqparams VALUES ('24', 'tp', '图片', null, null, null, null, '7');

-------------------------------------------------- 2014-04-01 -----------------------------------------------------------------------------

create table t_residence(
residenceid integer primary key not null,
name varchar(254),
SFZH varchar(254),
SJH varchar(254),
SZXPCJBM varchar(254),
expressage varchar(254),
bid date,
status varchar(254)
);


INSERT INTO t_services VALUES ('48', '1.5', '1045', '居住证网上办理', 
'insert into t_residence (residenceid,name, SFZH, SJH, SZXPCJBM, expressage, bid, status) values (PUB_SEQUENCE.NEXTVAL,?,?,?,?,?,sysdate,'''')' 
, null, 'update', 'local', null, null, '1', null, null);


INSERT INTO t_reqparams VALUES ('48', 'name', '名字', null, null, null, null, '1');
INSERT INTO t_reqparams VALUES ('48', 'SFZH', '身份证号', null, null, null, null, '2');
INSERT INTO t_reqparams VALUES ('48', 'SJH', '手机号', null, null, null, null, '3');
INSERT INTO t_reqparams VALUES ('48', 'SZXPCJBM', '数字相片采集编码', null, null, null, null, '4');
INSERT INTO t_reqparams VALUES ('48', 'expressage', '是否快递', null, null, null, null, '5');

INSERT INTO t_resparams VALUES ('48', 'result', '0:失败1:成功', null, null, null, '1');

-------------------------------------------------- 2014-04-02 -----------------------------------------------------------------------------

alter table t_member add (
DABH varchar(254),
JSZ2 varchar(254),
carNo2 varchar(254),
FDJH2 varchar(254),
HPZL2 varchar(254),
DABH2 varchar(254),
JSZ3 varchar(254),
carNo3 varchar(254),
FDJH3 varchar(254),
HPZL3 varchar(254),
DABH3 varchar(254)
);

update t_services set execsql='select PHONE,truename,JSZ,carNo,FDJH,HPZL,idcard,DABH,JSZ2,carNo2,FDJH2,HPZL2,DABH2,JSZ3,carNo3,FDJH3,HPZL3,DABH3 from t_member where loginname=?' where sid='23';

INSERT INTO t_resparams VALUES ('23', 'DABH', '驾驶证档案编号', null, null, null, '8');
INSERT INTO t_resparams VALUES ('23', 'JSZ2', '驾驶证号码2', null, null, null, '9');
INSERT INTO t_resparams VALUES ('23', 'carNo2', '车牌号码2', null, null, null, '10');
INSERT INTO t_resparams VALUES ('23', 'FDJH2', '发动机号(后6位)2', null, null, null, '11');
INSERT INTO t_resparams VALUES ('23', 'HPZL2', '号牌种类2', null, null, null, '12');
INSERT INTO t_resparams VALUES ('23', 'DABH2', '驾驶证档案编号2', null, null, null, '13');
INSERT INTO t_resparams VALUES ('23', 'JSZ3', '驾驶证号码3', null, null, null, '14');
INSERT INTO t_resparams VALUES ('23', 'carNo3', '车牌号码3', null, null, null, '15');
INSERT INTO t_resparams VALUES ('23', 'FDJH3', '发动机号(后6位)3', null, null, null, '16');
INSERT INTO t_resparams VALUES ('23', 'HPZL3', '号牌种类3', null, null, null, '17');
INSERT INTO t_resparams VALUES ('23', 'DABH3', '驾驶证档案编号3', null, null, null, '18');


update t_services set execsql='update t_member set truename=?, jsz=?, carno=?, fdjh=?, hpzl=?, idcard=?, 
DABH=?, jsz2=?, carno2=?, fdjh2=?, hpzl2=?, DABH2=?, jsz3=?, carno3=?, fdjh3=?, hpzl3=?, DABH3=? where phone=?' where sid='33';


INSERT INTO t_reqparams VALUES ('33', 'DABH', '驾驶证档案编号', null, null, null, null, '7');
INSERT INTO t_reqparams VALUES ('33', 'JSZ2', '驾驶证号码2', null, null, null, null, '8');
INSERT INTO t_reqparams VALUES ('33', 'carNo2', '车牌号码2', null, null, null, null, '9');
INSERT INTO t_reqparams VALUES ('33', 'FDJH2', '发动机号(后6位)2', null, null, null, null, '10');
INSERT INTO t_reqparams VALUES ('33', 'HPZL2', '号牌种类2', null, null, null, null, '11');
INSERT INTO t_reqparams VALUES ('33', 'DABH2', '驾驶证档案编号2', null, null, null, null, '12');
INSERT INTO t_reqparams VALUES ('33', 'JSZ3', '驾驶证号码3', null, null, null, null, '13');
INSERT INTO t_reqparams VALUES ('33', 'carNo3', '车牌号码3', null, null, null, null, '14');
INSERT INTO t_reqparams VALUES ('33', 'FDJH3', '发动机号(后6位)3', null, null, null, null, '15');
INSERT INTO t_reqparams VALUES ('33', 'HPZL3', '号牌种类3', null, null, null, null, '16');
INSERT INTO t_reqparams VALUES ('33', 'DABH3', '驾驶证档案编号3', null, null, null, null, '17');
update t_reqparams set seq='18' where sid='33' and reqid='phoneNo';

--------------------------------------------论坛-------------------------------------------------------

create table t_reply  (
   rid                  INTEGER                         not null,
   tid                  INTEGER,
   content              VARCHAR2(254),
   createtime           DATE,
   username             VARCHAR2(254),
   constraint PK_T_REPLY primary key (rid)
);
create table t_topic  (
   tid                  INTEGER                         not null,
   username             VARCHAR2(254),
   title                VARCHAR2(254),
   content              VARCHAR2(254),
   createtime           DATE,
   status               INTEGER,
   type                 INTEGER,
   constraint PK_T_TOPIC primary key (tid)
);

INSERT INTO t_services (sid, version, serviceId, serviceName, execSql, isDynamicSql, SqlType, dataSourceID, isPage, returnTotal, returnSingle, returnList, returnCustomize) 
VALUES (44, '1.5', '1041', '民意警务论坛创建话题', 'insert into t_topic(tid,title,content,username,createtime,status,type) values(PUB_SEQUENCE.NEXTVAL,?,?,?,sysdate,1,0)', '', 'update', 'local', '', '', '1', '', '');

INSERT INTO t_reqparams (sid, reqid, reqname, isMustin, reqtype, reqlen, reqformat, seq) 
VALUES (44, 'title', '标题', null, NULL, NULL, NULL, 1);
INSERT INTO t_reqparams (sid, reqid, reqname, isMustin, reqtype, reqlen, reqformat, seq) 
VALUES (44, 'content', '内容', null, NULL, NULL, NULL, 2);
INSERT INTO t_reqparams (sid, reqid, reqname, isMustin, reqtype, reqlen, reqformat, seq) 
VALUES (44, 'username', '用户名', null, NULL, NULL, NULL, 3);

insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (44, 'result', '0:失败1:成功', '', '', '', 1);

INSERT INTO t_services (sid, version, serviceId, serviceName, execSql, isDynamicSql, SqlType, dataSourceID, isPage, returnTotal, returnSingle, returnList, returnCustomize) 
VALUES (45, '1.5', '1042', '民意警务论坛提交评论', 'insert into t_reply (rid,tid,content,username,createtime) values(PUB_SEQUENCE.NEXTVAL,?,?,?,sysdate)', '', 'update', 'local', '', '', '1', '', '');


INSERT INTO t_reqparams (sid, reqid, reqname, isMustin, reqtype, reqlen, reqformat, seq) 
VALUES (45, 'tid', '话题ID', null, NULL, NULL, NULL, 1);
INSERT INTO t_reqparams (sid, reqid, reqname, isMustin, reqtype, reqlen, reqformat, seq) 
VALUES (45, 'content', '内容', null, NULL, NULL, NULL, 2);
INSERT INTO t_reqparams (sid, reqid, reqname, isMustin, reqtype, reqlen, reqformat, seq) 
VALUES (45, 'username', '用户名', null, NULL, NULL, NULL, 3);

insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (45, 'result', '0:失败1:成功', '', '', '', 1);

insert into t_services (SID, VERSION, SERVICEID, SERVICENAME, EXECSQL, ISDYNAMICSQL, SQLTYPE, DATASOURCEID, ISPAGE, RETURNTOTAL, RETURNSINGLE, RETURNLIST, RETURNCUSTOMIZE)
values (46, '1.5', '1043', '民意警务论坛话题查询', 'select tid,username,title,content,createtime from t_topic order by createtime DESC', '', 'query', 'local', '1', '', '', '1', '');

insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (46, 'tid', '话题id', '', '', '', 1);
insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (46, 'username', '用户名', '', '', '', 2);
insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (46, 'title', '标题', '', '', '', 3);
insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (46, 'content', '内容', '', '', '', 4);
insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (46, 'createtime', '创建时间', '', '', '', 5);

insert into t_services (SID, VERSION, SERVICEID, SERVICENAME, EXECSQL, ISDYNAMICSQL, SQLTYPE, DATASOURCEID, ISPAGE, RETURNTOTAL, RETURNSINGLE, RETURNLIST, RETURNCUSTOMIZE)
values (47, '1.5', '1044', '民意警务论坛评论查询', 'select username,content,createtime from t_reply where tid = ? order by createtime desc', '', 'query', 'local', '1', '', '', '1', '');

insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (47, 'username', '用户名', '', '', '', 1);
insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (47, 'content', '内容', '', '', '', 2);
insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (47, 'createtime', '创建时间', '', '', '', 3);

INSERT INTO t_reqparams (sid, reqid, reqname, isMustin, reqtype, reqlen, reqformat, seq) 
VALUES (47, 'tid', '话题id', null, NULL, NULL, NULL, 1);


---------------------------------------首页图片---------------------------------

alter table t_info add(pic blob);

insert into t_services (SID, VERSION, SERVICEID, SERVICENAME, EXECSQL, ISDYNAMICSQL, SQLTYPE, DATASOURCEID, ISPAGE, RETURNTOTAL, RETURNSINGLE, RETURNLIST, RETURNCUSTOMIZE)
values (49, '1.5', '1046', '图片新闻查询', 'select case when type=0 then ''今日发布'' when type=1 then ''交通状况'' when type=2 then ''警方提醒'' when type=3 then ''防范支招'' when type=4 then ''请您协助'' when type=5 then ''交通管制'' when type=6 then ''计划事件'' when type=7 then ''突发事件'' when type=8 then ''青少年警讯'' when type=9 then ''通缉令'' else ''其他'' end as type ,title,content,author,createtime,pic,infoid from t_info where pic is not null and rownum <=5 order by createtime desc', '', 'query', 'local', '', '', '', '1', '');

insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (49, 'type', '类型', '', '', '', 1);
insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (49, 'title', '标题', '', '', '', 2);
insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (49, 'content', '内容', '', '', '', 3);
insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (49, 'author', '作者', '', '', '', 4);
insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (49, 'createtime', '创建时间', '', '', '', 5);
insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (49, 'pic', '图片', 'byte', '', '', 6);
insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (49, 'infoid', '标识', '', '', '', 7);
------------------------------视频快照-------------------------------------
create table t_flash  (
   fid                  INTEGER                         not null,
   picpath              VARCHAR2(254),
   loca                 VARCHAR2(254),
   position             VARCHAR2(254),
   status               INTEGER,
   updatetime           DATE,
   piccode              VARCHAR2(254),
   innerpath            VARCHAR2(254),
   constraint PK_T_FLASH primary key (fid)
);

insert into t_services (SID, VERSION, SERVICEID, SERVICENAME, EXECSQL, ISDYNAMICSQL, SQLTYPE, DATASOURCEID, ISPAGE, RETURNTOTAL, RETURNSINGLE, RETURNLIST, RETURNCUSTOMIZE)
values (50, '1.5', '1047', '视频快照', 'select picpath,loca,position from t_flash where status =1 ', '', 'query', 'local', '1', '', '', '1', '');


insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (50, 'picpath', '图片路径', '', '', '', 1);
insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (50, 'loca', '地点', '', '', '', 2);
insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (50, 'position', '坐标位置', '', '', '', 3);
insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (50, 'piccode', '图片代号', '', '', '', 4);

insert into t_services (SID, VERSION, SERVICEID, SERVICENAME, EXECSQL, ISDYNAMICSQL, SQLTYPE, DATASOURCEID, ISPAGE, RETURNTOTAL, RETURNSINGLE, RETURNLIST, RETURNCUSTOMIZE)
values (51, '1.5', '1048', '视频快照(按照地点关键字查找)', 'select * from t_flash where loca like ''%''||?||''%'' and status = 1', '', 'query', 'local', '1', '', '', '1', '');


insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (51, 'picpath', '图片路径', '', '', '', 1);
insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (51, 'loca', '地点', '', '', '', 2);
insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (51, 'position', '坐标位置', '', '', '', 3);
insert into t_resparams (SID, RESID, RESNAME, RESTYPE, RESLEN, RESFORMAT, SEQ)
values (51, 'piccode', '图片代号', '', '', '', 4);

INSERT INTO t_reqparams (sid, reqid, reqname, isMustin, reqtype, reqlen, reqformat, seq) 
VALUES (51, 'loca', '地点', null, NULL, NULL, NULL, 1);
---------------------用户权限--------------------

update t_servicecustomer set servicelist = '1022,10010,10011,10012,1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016,1017,1018,1019,1020,1021,1024,1026,1028,1029,1030,1031,1032,1033,1034,1035,1036,1037,1038,1039,1040,1041,1042,1043,1044,1045,1046,1047,1048' where custid = 1100011 or custid = 1100012 or custid = 1100013
